CREATE OR REPLACE EDITIONABLE FUNCTION "SCOTT"."FNC_JUDGE_STOCK_WEEK_MA_TREND" (p_begin_date in varchar2,
                                                          p_end_date   varchar2,
                                                          p_stock_code in varchar2)
  return number is
  /* 判断某一只股票在某一周的均线趋势。
  如果是多头排列，则返回1，如果是空头排列，则返回-1，如果既不是多头排列也不是空头排列，则返回0 */
  result number;
  -- 5日均线
  v_ma5 number;
  -- 10日均线
  v_ma10 number;
  -- 20日均线
  v_ma20 number;
  -- 60日均线
  v_ma60 number;
  -- 120日均线
  v_ma120 number;
  -- 250日均线
  v_ma250 number;
begin
  -- 查询ma5, ma10, ma20,  ma60,  ma120,  ma250
  select t.ma5, t.ma10, t.ma20, t.ma60, t.ma120, t.ma250
    into v_ma5, v_ma10, v_ma20, v_ma60, v_ma120, v_ma250
    from stock_week t
   where t.code_ = p_stock_code
     and t.begin_date between to_date(p_begin_date, 'yyyy-mm-dd') and
         to_date(p_end_date, 'yyyy-mm-dd')
     and t.end_date between to_date(p_begin_date, 'yyyy-mm-dd') and
         to_date(p_end_date, 'yyyy-mm-dd');

  -- 判断移动平均线的趋势（考虑有的列为null的情况）
  if (v_ma5 is null and v_ma10 is null and v_ma20 is null and
     v_ma60 is null and v_ma120 is null and v_ma250 is null) or
     (v_ma5 is not null and v_ma10 is null and v_ma20 is null and
     v_ma60 is null and v_ma120 is null and v_ma250 is null) then
    result := 0;
  end if;

  if v_ma5 is not null and v_ma10 is not null and v_ma20 is null and
     v_ma60 is null and v_ma120 is null and v_ma250 is null then
    if v_ma5 > v_ma10 then
      result := 1;
    elsif v_ma5 < v_ma10 then
      result := -1;
    else
      result := 0;
    end if;
  end if;

  if v_ma5 is not null and v_ma10 is not null and v_ma20 is not null and
     v_ma60 is null and v_ma120 is null and v_ma250 is null then
    if v_ma5 > v_ma10 and v_ma10 > v_ma20 then
      result := 1;
    elsif v_ma5 < v_ma10 and v_ma10 < v_ma20 then
      result := -1;
    else
      result := 0;
    end if;
  end if;

  if v_ma5 is not null and v_ma10 is not null and v_ma20 is not null and
     v_ma60 is not null and v_ma120 is null and v_ma250 is null then
    if v_ma5 > v_ma10 and v_ma10 > v_ma20 and v_ma20 > v_ma60 then
      result := 1;
    elsif v_ma5 < v_ma10 and v_ma10 < v_ma20 and v_ma20 < v_ma60 then
      result := -1;
    else
      result := 0;
    end if;
  end if;

  if v_ma5 is not null and v_ma10 is not null and v_ma20 is not null and
     v_ma60 is not null and v_ma120 is not null and v_ma250 is null then
    if v_ma5 > v_ma10 and v_ma10 > v_ma20 and v_ma20 > v_ma60 and
       v_ma60 > v_ma120 then
      result := 1;
    elsif v_ma5 < v_ma10 and v_ma10 < v_ma20 and v_ma20 < v_ma60 and
          v_ma60 < v_ma120 then
      result := -1;
    else
      result := 0;
    end if;
  end if;

  if v_ma5 is not null and v_ma10 is not null and v_ma20 is not null and
     v_ma60 is not null and v_ma120 is not null and v_ma250 is not null then
    if v_ma5 > v_ma10 and v_ma10 > v_ma20 and v_ma20 > v_ma60 and
       v_ma60 > v_ma120 and v_ma120 > v_ma250 then
      result := 1;
    elsif v_ma5 < v_ma10 and v_ma10 < v_ma20 and v_ma20 < v_ma60 and
          v_ma60 < v_ma120 and v_ma120 < v_ma250 then
      result := -1;
    else
      result := 0;
    end if;
  end if;

  return(result);
end FNC_JUDGE_STOCK_WEEK_MA_TREND;